﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using NPOI;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

using CashFlow.Db.Entity;
using CashFlow.Db.DAL;
using SysTool._00_Def;

namespace SysTool
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

        private void Form2_Load(object sender, EventArgs e)
        {

        }

        private void Form2_Shown(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            DialogResult dr = openFileDialog1.ShowDialog();
            if (dr == DialogResult.OK)
            {
                textBox1.Text = openFileDialog1.FileName;
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            DialogResult dr = openFileDialog2.ShowDialog();
            if (dr == DialogResult.OK)
            {
                textBox2.Text = openFileDialog2.FileName;
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            var ls = new List<ClearInfoEntity>();

            try
            {
                using (FileStream fs = File.Open(textBox1.Text.Trim(), FileMode.Open, FileAccess.Read, FileShare.Read))
                {
                    IWorkbook workbook = new XSSFWorkbook(fs);
                    if (workbook != null)
                    {
                        ISheet sheet = workbook.GetSheetAt(0);
                        if (sheet != null)
                        {
                            int count = sheet.LastRowNum;
                            IRow fstRow = sheet.GetRow(0);
                            for (int i = 1; i <= count; i++)
                            {
                                IRow row = sheet.GetRow(i);
                                if (row == null)
                                {
                                    break;
                                }
                                if (row.GetCell(0) != null)
                                {
                                    if (row.GetCell(0).CellType == CellType.Blank)
                                    {
                                        break;
                                    }
                                }

                                for (int j = 2; j < 50; j++)
                                {
                                    string str = string.Format("{0}:{1}", i + 1, j + 1);
                                    System.Diagnostics.Trace.WriteLine(str);

                                    ICell cell = row.GetCell(j);
                                    if (cell != null && cell.CellType != CellType.Blank && cell.CellType != CellType.Unknown)
                                    {
                                        if (cell.CellType == CellType.Numeric)
                                        {
                                            decimal amount = (decimal)cell.NumericCellValue;
                                            if (amount > 0)
                                            {
                                                string name = row.GetCell(1).StringCellValue;
                                                var date2 = fstRow.GetCell(j).StringCellValue;
                                                var date = ConvertToDate(date2);
                                                ls.Add(new ClearInfoEntity
                                                {
                                                    Name = name,
                                                    Amount = amount,
                                                    Date = date.ToString("yyyy-MM-dd"),
                                                    Shop = "晟钰满堂"
                                                });
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }

                using (var db = SqlSugarHelper.GetDb())
                {
                    foreach (var item in ls)
                    {
                        var entity = db.Queryable<ClearInfoEntity>()
                            .Where(x => x.Name == item.Name && x.Date == item.Date)
                            .First();
                        if (entity != null)
                        {
                            item.Id = entity.Id;
                            db.Updateable<ClearInfoEntity>(item).ExecuteCommand();
                        }
                        else
                        {
                            db.Insertable<ClearInfoEntity>(item).ExecuteCommand();
                        }
                    }
                }

                MessageBox.Show("清算成功", "SysTool", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "SysTool", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        private DateTime ConvertToDate(string text)
        {
            string[] arr = text.Split('.');
            int year = 0;
            int month = Convert.ToInt32(arr[0]);
            int day = Convert.ToInt32(arr[1]);
            if (month == 1)
            {
                year = 2024;
            }
            else
            {
                year = 2023;
            }
            return new DateTime(year, month, day);
        }

        private void button4_Click(object sender, EventArgs e)
        {
            var ls = new List<ClearInfoEntity>();

            try
            {
                using (FileStream fs = File.Open(textBox2.Text.Trim(), FileMode.Open, FileAccess.Read, FileShare.Read))
                {
                    IWorkbook workbook = new XSSFWorkbook(fs);
                    if (workbook != null)
                    {
                        ISheet sheet = workbook.GetSheetAt(0);
                        if (sheet != null)
                        {
                            int count = sheet.LastRowNum;
                            IRow fstRow = sheet.GetRow(0);
                            for (int i = 1; i <= count; i++)
                            {
                                IRow row = sheet.GetRow(i);
                                if (row == null)
                                {
                                    break;
                                }
                                if (row.GetCell(0) != null)
                                {
                                    if (row.GetCell(0).CellType == CellType.Blank)
                                    {
                                        break;
                                    }
                                }

                                for (int j = 2; j < 48; j++)
                                {
                                    string str = string.Format("{0}:{1}", i + 1, j + 1);
                                    System.Diagnostics.Trace.WriteLine(str);

                                    ICell cell = row.GetCell(j);
                                    if (cell != null && cell.CellType != CellType.Blank && cell.CellType != CellType.Unknown)
                                    {
                                        if (cell.CellType == CellType.Numeric)
                                        {
                                            decimal amount = (decimal)cell.NumericCellValue;
                                            if (amount > 0)
                                            {
                                                string name = row.GetCell(1).StringCellValue;
                                                string date2 = fstRow.GetCell(j).StringCellValue;
                                                var date = ConvertToDate(date2);
                                                ls.Add(new ClearInfoEntity
                                                {
                                                    Name = name,
                                                    Amount = amount,
                                                    Date = date.ToString("yyyy-MM-dd"),
                                                    Shop = "金钰满堂"
                                                });
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }

                using (var db = SqlSugarHelper.GetDb())
                {
                    foreach (var item in ls)
                    {
                        var entity = db.Queryable<ClearInfoEntity>()
                            .Where(x => x.Name == item.Name && x.Date == item.Date)
                            .First();
                        if (entity != null)
                        {
                            item.Id = entity.Id;
                            db.Updateable<ClearInfoEntity>(item).ExecuteCommand();
                        }
                        else
                        {
                            db.Insertable<ClearInfoEntity>(item).ExecuteCommand();
                        }
                    }
                }

                MessageBox.Show("清算成功", "SysTool", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "SysTool", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            export("晟钰满堂");
        }

        private int GetServiceFee(IList<UserInfoAndReferenceUserInfo> users
            , IList<ClearInfoEntity> clearinfos
            , string name)
        {
            var item = users.Where(x => x.UserInfo.Name == name).FirstOrDefault();
            if (item != null)
            {
                var ls = users
                    .Where(x => x.UserInfo.ReferenceInvitationCode == item.UserInfo.InvitationCode)
                    .Select(x => x.UserInfo.Name)
                    .ToList();
                var sum = clearinfos.Where(x => ls.Contains(x.Name)).Sum(x => x.Amount);
                var sum2 = sum * 0.005M;
                return (int)Math.Round(sum2);
            }

            return 0;
        }

        private void button6_Click(object sender, EventArgs e)
        {
            export("金钰满堂");
        }

        private void button7_Click(object sender, EventArgs e)
        {
            export("福满堂");
        }

        private void export(string shop)
        {
            try
            {
                var users = new List<UserInfoAndReferenceUserInfo>();
                var clearInfos = new List<ClearInfoEntity>();
                using (var db = SqlSugarHelper.GetDb())
                {
                    users = db.Queryable<UserInfoEntity>()
                        .InnerJoin<UserInfoEntity>((u1, u2) => u1.ReferenceInvitationCode == u2.InvitationCode)
                        .Select((u1, u2) => new UserInfoAndReferenceUserInfo
                        {
                            UserInfo = u1,
                            ReferenceUserInfo = u2
                        })
                        .ToList();
                    clearInfos = db.Queryable<ClearInfoEntity>()
                        .Where(x => x.Shop == shop)
                        .ToList();
                }


                var workbook = new XSSFWorkbook();
                var style = workbook.CreateCellStyle();
                style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                style.VerticalAlignment = VerticalAlignment.Center;
                var font = workbook.CreateFont();
                font.FontHeightInPoints = 14;
                style.SetFont(font);

                #region 订单明细
                {
                    var sheet = workbook.CreateSheet("订单明细");
                    sheet.CreateFreezePane(3, 1);
                    sheet.SetColumnWidth(0, 15 * 256);
                    sheet.SetColumnWidth(1, 25 * 256);
                    sheet.SetColumnWidth(2, 15 * 256);

                    #region 列标题
                    {
                        var style2 = workbook.CreateCellStyle();
                        style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        style2.VerticalAlignment = VerticalAlignment.Center;
                        var font2 = workbook.CreateFont();
                        font2.FontHeightInPoints = 16;
                        font2.IsBold = true;
                        style2.SetFont(font2);

                        var row = sheet.CreateRow(0);
                        row.Height = 40 * 20;

                        var cell = row.CreateCell(0);
                        cell.CellStyle = style2;
                        cell.SetCellType(CellType.String);
                        cell.SetCellValue("姓名");

                        var cell2 = row.CreateCell(1);
                        cell2.CellStyle = style2;
                        cell2.SetCellType(CellType.String);
                        cell2.SetCellValue("日期");

                        var cell3 = row.CreateCell(2);
                        cell3.CellStyle = style2;
                        cell3.SetCellType(CellType.String);
                        cell3.SetCellValue("金额");
                    }
                    #endregion

                    #region 行数据
                    {
                        var ls = clearInfos.OrderBy(x => x.Name).ThenBy(x => x.Date).ToList();
                        for (int i = 0; i < ls.Count; i++)
                        {
                            var item = ls.ElementAt(i);
                            var row = sheet.CreateRow(i + 1);
                            row.Height = 30 * 20;

                            var cell = row.CreateCell(0);
                            cell.CellStyle = style;
                            cell.SetCellType(CellType.String);
                            cell.SetCellValue(item.Name);

                            var cell2 = row.CreateCell(1);
                            cell2.CellStyle = style;
                            cell2.SetCellType(CellType.String);
                            cell2.SetCellValue(item.Date);

                            var cell3 = row.CreateCell(2);
                            cell3.CellStyle = style;
                            cell3.SetCellType(CellType.Numeric);
                            cell3.SetCellValue((double)item.Amount);
                        }
                    }
                    #endregion
                }
                #endregion

                #region 收入明细
                {
                    var sheet = workbook.CreateSheet("收入明细");
                    sheet.CreateFreezePane(3, 1);
                    sheet.SetColumnWidth(0, 15 * 256);  // 姓名
                    sheet.SetColumnWidth(1, 18 * 256);  // 订单总数
                    sheet.SetColumnWidth(2, 18 * 256);  // 订单总金额
                    sheet.SetColumnWidth(3, 18 * 256);  // 订单总利润
                    sheet.SetColumnWidth(4, 18 * 256);  // 推荐服务费
                    sheet.SetColumnWidth(5, 15 * 256);  // 冲锋红包
                    sheet.SetColumnWidth(7, 15 * 256);  // 总收入
                    sheet.SetColumnWidth(6, 25 * 256);  // 技术服务费
                    sheet.SetColumnWidth(8, 15 * 256);  // 总扣除

                    #region 列标题
                    {
                        var style2 = workbook.CreateCellStyle();
                        style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        style2.VerticalAlignment = VerticalAlignment.Center;
                        var font2 = workbook.CreateFont();
                        font2.FontHeightInPoints = 16;
                        font2.IsBold = true;
                        style2.SetFont(font2);

                        var row = sheet.CreateRow(0);
                        row.Height = 40 * 20;

                        var cell = row.CreateCell(0);
                        cell.CellStyle = style2;
                        cell.SetCellType(CellType.String);
                        cell.SetCellValue("姓名");

                        var cell2 = row.CreateCell(1);
                        cell2.CellStyle = style2;
                        cell2.SetCellType(CellType.String);
                        cell2.SetCellValue("订单总数");

                        var cell3 = row.CreateCell(2);
                        cell3.CellStyle = style2;
                        cell3.SetCellType(CellType.String);
                        cell3.SetCellValue("订单总金额");

                        var cell4 = row.CreateCell(3);
                        cell4.CellStyle = style2;
                        cell4.SetCellType(CellType.String);
                        cell4.SetCellValue("订单总利润");

                        var cell5 = row.CreateCell(4);
                        cell5.CellStyle = style2;
                        cell5.SetCellType(CellType.String);
                        cell5.SetCellValue("推荐服务费");

                        var cell6 = row.CreateCell(5);
                        cell6.CellStyle = style2;
                        cell6.SetCellType(CellType.String);
                        cell6.SetCellValue("冲锋红包");

                        var cell7 = row.CreateCell(6);
                        cell7.CellStyle = style2;
                        cell7.SetCellType(CellType.String);
                        cell7.SetCellValue("总收入");

                        var cell8 = row.CreateCell(7);
                        cell8.CellStyle = style2;
                        cell8.SetCellType(CellType.String);
                        cell8.SetCellValue("技术服务费");

                        var cell9 = row.CreateCell(8);
                        cell9.CellStyle = style2;
                        cell9.SetCellType(CellType.String);
                        cell9.SetCellValue("总扣除");
                    }
                    #endregion

                    #region 行数据
                    {
                        var ls = clearInfos.GroupBy(x => x.Name).ToList();
                        var sumService = 0; // 推荐服务费总和
                        for (int i = 0; i < ls.Count; i++)
                        {
                            var item = ls.ElementAt(i);
                            var row = sheet.CreateRow(i + 1);
                            row.Height = 30 * 20;

                            var cell = row.CreateCell(0);
                            cell.CellStyle = style;
                            cell.SetCellType(CellType.String);
                            cell.SetCellValue(item.Key);

                            // 订单总数
                            var cell2 = row.CreateCell(1);
                            cell2.CellStyle = style;
                            cell2.SetCellType(CellType.Numeric);
                            cell2.SetCellValue(item.Count());

                            // 订单总金额
                            var cell3 = row.CreateCell(2);
                            cell3.CellStyle = style;
                            cell3.SetCellType(CellType.Numeric);
                            var value3 = (double)Math.Round(item.Sum(x => x.Amount));
                            cell3.SetCellValue(value3);

                            // 订单利润
                            var cell4 = row.CreateCell(3);
                            cell4.CellStyle = style;
                            cell4.SetCellType(CellType.Numeric);
                            var value4 = (double)Math.Round(item.Sum(x => x.Amount) * 0.015M);
                            cell4.SetCellValue(value4);

                            // 推荐服务费
                            var cell5 = row.CreateCell(4);
                            cell5.CellStyle = style;
                            cell5.SetCellType(CellType.Numeric);
                            var value5 = GetServiceFee(users, clearInfos, item.Key);
                            sumService += value5;
                            cell5.SetCellValue(value5);

                            // 冲锋红包
                            var cell6 = row.CreateCell(5);
                            cell6.CellStyle = style;
                            cell6.SetCellType(CellType.Numeric);
                            var value6 = (double)Math.Round(item.Count() * 6.66M);
                            cell6.SetCellValue(value6);

                            // 总收入
                            var cell7 = row.CreateCell(6);
                            cell7.CellStyle = style;
                            cell7.SetCellType(CellType.Numeric);
                            var value7 = value4 + value5 + value6;
                            cell7.SetCellValue(value7);

                            // 技术服务费
                            var cell8 = row.CreateCell(7);
                            cell8.CellStyle = style;
                            cell8.SetCellType(CellType.Numeric);
                            var value8 = (double)Math.Round(item.Sum(x => x.Amount) * 0.008M);
                            cell8.SetCellValue(value8);

                            // 总扣除
                            var cell9 = row.CreateCell(8);
                            cell9.CellStyle = style;
                            cell9.SetCellType(CellType.Numeric);
                            var value9 = value7 + value8;
                            cell9.SetCellValue(value9);
                        }

                        #region 合计
                        {
                            var row = sheet.CreateRow(ls.Count + 1);
                            row.Height = 30 * 20;

                            var cell = row.CreateCell(0);
                            cell.CellStyle = style;
                            cell.SetCellType(CellType.String);
                            cell.SetCellValue("合计");

                            // 订单总数
                            var cell2 = row.CreateCell(1);
                            cell2.CellStyle = style;
                            cell2.SetCellType(CellType.Numeric);
                            var value2 = ls.Sum(x => x.Count());
                            cell2.SetCellValue((double)value2);

                            // 订单总金额
                            var cell3 = row.CreateCell(2);
                            cell3.CellStyle = style;
                            cell3.SetCellType(CellType.Numeric);
                            var value3 = Math.Round(ls.Sum(x => x.Sum(y => y.Amount)));
                            cell3.SetCellValue((double)value3);

                            // 订单利润
                            var cell4 = row.CreateCell(3);
                            cell4.CellStyle = style;
                            cell4.SetCellType(CellType.Numeric);
                            var value4 = (double)Math.Round(value3 * 0.015M);
                            cell4.SetCellValue(value4);

                            // 推荐服务费
                            var cell5 = row.CreateCell(4);
                            cell5.CellStyle = style;
                            cell5.SetCellType(CellType.Numeric);
                            var value5 = sumService;
                            cell5.SetCellValue(value5);

                            // 冲锋红包
                            var cell6 = row.CreateCell(5);
                            cell6.CellStyle = style;
                            cell6.SetCellType(CellType.Numeric);
                            var value6 = (double)Math.Round(value2 * 6.66M);
                            cell6.SetCellValue(value6);

                            // 总收入
                            var cell7 = row.CreateCell(6);
                            cell7.CellStyle = style;
                            cell7.SetCellType(CellType.Numeric);
                            var value7 = value4 + (double)value5 + value6;
                            cell7.SetCellValue(value7);

                            // 技术服务费
                            var cell8 = row.CreateCell(7);
                            cell8.CellStyle = style;
                            cell8.SetCellType(CellType.Numeric);
                            var value8 = (double)Math.Round(value3 * 0.008M);
                            cell8.SetCellValue(value8);

                            // 总扣除
                            var cell9 = row.CreateCell(8);
                            cell9.CellStyle = style;
                            cell9.SetCellType(CellType.Numeric);
                            var value9 = value7 + value8;
                            cell9.SetCellValue(value9);
                        }
                        #endregion
                    }
                    #endregion
                }
                #endregion

                string path = string.Format("{0}\\{1}.xlsx",
                    Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory),
                    shop);
                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    var buf = ms.GetBuffer();

                    FileStream fs = File.Open(path, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);
                    fs.SetLength(0);
                    fs.Position = 0;
                    fs.Write(buf, 0, buf.Length);
                    fs.Flush();
                    fs.Close();
                }

                MessageBox.Show("导出成功", "SysTool", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "SysTool", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }
    }
}
